// com.example.demo_text.GradeDAO
package com.example.demo_text;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class GradeDAO {

    /**
     * 获取所有成绩记录
     */
    public static List<Grade> getAllGrades() {
        List<Grade> grades = new ArrayList<>();
        String sql = "SELECT * FROM grades ORDER BY update_time DESC";

        try (Connection conn = UserDAO.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                Grade grade = new Grade();
                grade.setStudentId(rs.getInt("student_id"));
                grade.setCourseId(rs.getInt("course_id"));
                grade.setScore(rs.getDouble("score"));
                grade.setUpdateTime(rs.getDate("update_time"));
                grades.add(grade);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return grades;
    }

    /**
     * 根据学生和课程ID查询成绩
     */
    public static Grade getGradeByStudentAndCourse(int studentId, int courseId) {
        String sql = "SELECT * FROM grades WHERE student_id = ? AND course_id = ?";
        try (Connection conn = UserDAO.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, studentId);
            pstmt.setInt(2, courseId);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    Grade grade = new Grade();
                    grade.setStudentId(rs.getInt("student_id"));
                    grade.setCourseId(rs.getInt("course_id"));
                    grade.setScore(rs.getDouble("score"));
                    grade.setUpdateTime(rs.getDate("update_time"));
                    return grade;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 更新成绩（不存在则插入）
     */
    public static boolean saveOrUpdateGrade(int studentId, int courseId, double score) {
        String checkSql = "SELECT COUNT(*) FROM grades WHERE student_id = ? AND course_id = ?";
        String insertSql = "INSERT INTO grades(student_id, course_id, score) VALUES(?,?,?)";
        String updateSql = "UPDATE grades SET score = ?, update_time = NOW() WHERE student_id = ? AND course_id = ?";

        try (Connection conn = UserDAO.getConnection()) {
            // 检查是否存在记录
            try (PreparedStatement checkStmt = conn.prepareStatement(checkSql)) {
                checkStmt.setInt(1, studentId);
                checkStmt.setInt(2, courseId);
                try (ResultSet rs = checkStmt.executeQuery()) {
                    if (rs.next() && rs.getInt(1) > 0) {
                        // 更新现有记录
                        try (PreparedStatement updateStmt = conn.prepareStatement(updateSql)) {
                            updateStmt.setDouble(1, score);
                            updateStmt.setInt(2, studentId);
                            updateStmt.setInt(3, courseId);
                            return updateStmt.executeUpdate() > 0;
                        }
                    } else {
                        // 插入新记录
                        try (PreparedStatement insertStmt = conn.prepareStatement(insertSql)) {
                            insertStmt.setInt(1, studentId);
                            insertStmt.setInt(2, courseId);
                            insertStmt.setDouble(3, score);
                            return insertStmt.executeUpdate() > 0;
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }
}
